package javaroke.dao;

import java.sql.Connection;
import java.sql.ResultSet;

import javaroke.modelo.Cliente;
import javaroke.modelo.Local;
import javaroke.modelo.Persona;
import javaroke.modelo.Reserva;
import javaroke.modelo.Sala;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import javaroke.excepcion.DAOExcepcion;
import javaroke.util.ConexionBD;

public class ReservaDAO extends BaseDAO {
	
	public Collection<Reserva> listarReservas()
			throws DAOExcepcion {
		//String query = "select idreserva, fecha, descripcion from Reserva where IdCliente like ? and elog = 0";
		String query = "select a.idreserva, a.fecha, a.descripcion, a.idsala, a.idcliente , b.nombre as sala, c.dni, d.nombres, d.paterno, d.materno from reserva a, sala b, cliente c, persona d where a.idsala=b.idsala and a.idcliente=c.idcliente and c.dni=d.dni and a.elog = true";
		Collection<Reserva> lista = new ArrayList<Reserva>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			//stmt.setString(1, "%" + IdCliente + "%");
			rs = stmt.executeQuery();
			while (rs.next()) {
				Reserva vo = new Reserva();
				vo.setIdreserva(rs.getInt("idreserva"));
				vo.setFecha(rs.getString("fecha"));
				vo.setDescripcion(rs.getString("descripcion"));
				
				Sala s = new Sala();
				s.setIdsala(rs.getInt("idsala"));
				s.setNombre(rs.getString("sala"));
				
				Persona p = new Persona();
				p.setDni(rs.getInt("dni"));
				p.setNombres(rs.getString("nombres"));
				p.setPaterno(rs.getString("paterno"));
				p.setMaterno(rs.getString("materno"));

				Cliente c = new Cliente();
				c.setIdcliente(rs.getInt("idcliente"));				
				c.setDni(p);

				vo.setSala(s);
				vo.setCliente(c);
				lista.add(vo);
				
				System.out.println("----------->" + vo.getDescripcion());
				System.out.println("----------->" + vo.getSala().getNombre());
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		System.out.println(lista.size());
		return lista;
	}
	
	public Collection<Reserva> buscarPorDescripcion(String descripcion)
			throws DAOExcepcion {
		String query = "select r.idreserva,r.fecha,r.descripcion,r.idsala, s.nombre as nomsal, r.idcliente, c.correo, r.idlocal, l.nombre as nomloc from Reserva r, sala s, cliente c, local l where r.idsala= s.idsala and r.idcliente = c.idcliente and r.idlocal = l.idlocal and  r.descripcion like ? and r.elog = 0 order by r.fecha desc";
		Collection<Reserva> lista = new ArrayList<Reserva>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, "%" + descripcion + "%");
			rs = stmt.executeQuery();
			while (rs.next()) {
				Reserva vo = new Reserva();
				vo.setIdreserva(rs.getInt("idreserva"));
				vo.setFecha(rs.getString("fecha"));
				vo.setDescripcion(rs.getString("descripcion"));
				
				Sala sal = new Sala();
				sal.setIdsala(rs.getInt("idsala"));
				sal.setNombre(rs.getString("nomsal"));
				vo.setSala(sal);
				
				Cliente clie = new Cliente();
				clie.setIdcliente(rs.getInt("idcliente"));
				clie.setCorreo(rs.getString("correo"));
				vo.setCliente(clie);
				
				Local loc = new Local();
				loc.setIdlocal(rs.getInt("idlocal"));
				loc.setNombre(rs.getString("nomloc"));
				vo.setLocal(loc);	
				
				lista.add(vo);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		System.out.println(lista.size());
		return lista;
	}


	public Collection<Reserva> buscarPorIdCliente(String IdCliente)
			throws DAOExcepcion {
		String query = "select idreserva, fecha, descripcion from Reserva where IdCliente like ? and elog = 0";
		Collection<Reserva> lista = new ArrayList<Reserva>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, "%" + IdCliente + "%");
			rs = stmt.executeQuery();
			while (rs.next()) {
				Reserva vo = new Reserva();
				vo.setIdreserva(rs.getInt("idreserva"));
				vo.setFecha(rs.getString("fecha"));
				vo.setDescripcion(rs.getString("descripcion"));
				lista.add(vo);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		System.out.println(lista.size());
		return lista;
	}

	public Reserva insertar(Reserva vo) throws DAOExcepcion {
		String query = "insert into Reserva (fecha,descripcion,eLog,idsala,idcliente,idlocal) values (?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, vo.getFecha());
			stmt.setString(2, vo.getDescripcion());
			stmt.setInt(3, vo.getElog());
			stmt.setInt(4, vo.getSala().getIdsala());
			stmt.setInt(5, vo.getCliente().getIdcliente());
			stmt.setInt(6, vo.getLocal().getIdlocal());


			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar la Reserva");
			}
			// Obtener el ultimo id
			int id = 0;
			query = "select last_insert_id()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				id = rs.getInt(1);
			}
			vo.setIdreserva(id);

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}

	public Reserva obtener(int idreserva) throws DAOExcepcion {
		Reserva vo = new Reserva();
		Sala sal  = new Sala();
		Cliente cli = new Cliente();
		Local loc = new Local();
		
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select idreserva, fecha, descripcion, elog, idsala, idcliente, idlocal from Reserva where idreserva = ? and elog = 0";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, idreserva);
			rs = stmt.executeQuery();
			if (rs.next()) {
				vo.setIdreserva(rs.getInt(1));
				vo.setFecha(rs.getString(2));
				vo.setDescripcion(rs.getString(3));
				vo.setElog(rs.getInt(4));
				
				sal.setIdsala(rs.getInt(5));
				vo.setSala(sal);
				
				cli.setIdcliente(rs.getInt(6));
				vo.setCliente(cli);
				
				loc.setIdlocal(rs.getInt(7));
				vo.setLocal(loc);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}

	public void eliminar(int idreserva) throws DAOExcepcion {
		String query = "update Reserva set elog = 1 WHERE idreserva = ?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, idreserva);
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo eliminar la Reserva");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}

	public Reserva actualizar(Reserva vo) throws DAOExcepcion {
		String query = "update Reserva set fecha = ?, descripcion = ?, idsala = ? , idcliente = ? where idreserva = ?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, vo.getFecha());
			stmt.setString(2, vo.getDescripcion());
			stmt.setInt(3, vo.getSala().getIdsala());
			stmt.setInt(4, vo.getCliente().getIdcliente());
			stmt.setInt(5, vo.getIdreserva());
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar la Reserva");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}

	public Collection<Reserva> listar() throws DAOExcepcion {
		Collection<Reserva> c = new ArrayList<Reserva>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			String query = "select r.idreserva,r.fecha,r.descripcion, s.nombre as nomsal, c.correo, l.nombre as nomloc from Reserva r, sala s, cliente c, local l where r.idsala= s.idsala and r.idcliente = c.idcliente and r.idlocal = l.idlocal and r.elog = 0 order by r.fecha desc";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Reserva vo = new Reserva();
				vo.setIdreserva(rs.getInt("idreserva"));
				vo.setFecha(rs.getString("fecha"));
				vo.setDescripcion(rs.getString("descripcion"));
				
				Sala sal = new Sala();
				sal.setNombre(rs.getString("nomsal"));
				vo.setSala(sal);
				
				Cliente clie = new Cliente();
				clie.setCorreo(rs.getString("correo"));
				vo.setCliente(clie);
				
				Local loc = new Local();
				loc.setNombre(rs.getString("nomloc"));
				vo.setLocal(loc);	
				
				
				c.add(vo);
			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return c;
	}

}
